package gen.tools.delivery;

import gen.tools.CommitMediator;
import gen.tools.Widgets;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.util.IOUtils;
import org.tmatesoft.svn.core.SVNCommitInfo;
import org.tmatesoft.svn.core.SVNURL;
import org.tmatesoft.svn.core.auth.ISVNAuthenticationManager;
import org.tmatesoft.svn.core.io.ISVNEditor;
import org.tmatesoft.svn.core.io.SVNRepository;
import org.tmatesoft.svn.core.io.SVNRepositoryFactory;
import org.tmatesoft.svn.core.io.diff.SVNDeltaGenerator;
import org.tmatesoft.svn.core.wc.SVNWCUtil;

import com.google.gson.Gson;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

public class Deliver4SDEL extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public Deliver4SDEL() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		final Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/text");
		
		/*
		 * 1. 寫入需求
		 * 2. 記錄F部署
		 * 3. 記錄部署計劃
		 * 3. 將檔案寫入SVN CM OL 封裝檔 
		 * 4. 記錄交付事件
		 * 5. 簽入分公司的SVN Repo
		 * 6. 記錄"SVN交付記錄"
		 * 
		 **/
		
		
		final Fod fod = new Gson().fromJson( request.getParameter("fOrderDeliver"), Fod.class);
		final String empId = request.getParameter("empId");
		final String subsysId = request.getParameter("subsysId");
		final String ftpUrl = fod.getFtpURL();	//連線位址
		final String ftpId = fod.getFtpID();	//登入帳號
		final String ftpPwd = fod.getFtpPWD();	//登入密碼
		final String ftpPath = fod.getFtpPATH();	//切換的路徑
		
		
		final String tarUrl = "http://10.144.97.99/Arthur/";
		final String tarUsr = "Arthur";
		final String tarPwd = "arthur7382";
		
		String sql = "";

		Connection conn = null;
		Connection tarConn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int fDeplmainSN = 0;
		
		SVNRepository souRepo = null;
		SVNRepository tarRepo = null;
		ISVNEditor editor1 = null;
		ISVNEditor editor2 = null;
		SVNCommitInfo info1 = null;
		SVNCommitInfo info2 = null;
		InputStream input1 = null;
		InputStream input2 = null;
		final SVNDeltaGenerator deltaGenerator = new SVNDeltaGenerator();
		
		final JSch jsch = new JSch();
        JSch.setConfig("StrictHostKeyChecking", "no");
        Session session = null;
        ChannelSftp sftp = null;
		
		try {
			conn = wid.getConn();
			final int localSysemId = wid.getLocalSysemId();
			final int targetSystemId = 3;
			
			sql = "INSERT INTO fdeplmain (ftpIP,ftpPath,cDT) VALUES(?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, fod.getFtpURL() );
			ps.setString(2, fod.getFtpPATH() );
			ps.setTimestamp(3, wid.cDT() );
			ps.execute();
			fDeplmainSN = wid.queryMaxFdeplMainSN();	//新增了F部署的主檔，取得了序號
			
			
			//建立<分公司>資料庫連線
			Class.forName("com.mysql.jdbc.Driver");
			final String tarIp = "10.144.136.54";
			final int tarPort = 3306;
			final String tarDBname = "shield";
			final String url = "jdbc:mysql://"+tarIp+":"+tarPort+"/"+tarDBname;
			tarConn = DriverManager.getConnection( url,"Arthur","abcd1234" );
			
			
			//記錄部署計劃<研究院>
			final int localDeplMainSN = wid.getCurrentSN("deplmain"); //目前部署計劃的流水號
			final int tarDeplMainSN = wid.getTarCurrentSN("deplmain", tarConn); //目前部署計劃的流水號
			sql ="INSERT INTO deplmain (deplmainSN,sysSN,creator,createdDateTime,subsysid,tarDeplmainSN) VALUES (?,?,?,?,?,?)";
			ps = conn.prepareStatement( sql );
			ps.setInt(1, localDeplMainSN);
			ps.setInt(2, localSysemId);
			ps.setString(3, empId);
			ps.setTimestamp(4, wid.cDT() );
			ps.setString(5, subsysId );
			ps.setInt(6,tarDeplMainSN);
			ps.execute();
			
			//記錄部署計劃<分公司>
			ps = tarConn.prepareStatement( sql );
			ps.setInt(1, tarDeplMainSN);
			ps.setInt(2, targetSystemId);
			ps.setString(3, empId);
			ps.setTimestamp(4, wid.cDT() );
			ps.setString(5, subsysId );
			ps.setInt(6,localDeplMainSN);
			ps.execute();
			
			//產生交付事件<研究院>
			final int deliverySN = wid.getCurrentSN("deltask");	//取得交付事件的流水號
			final int tarDeliverySN = wid.getTarCurrentSN("deltask",tarConn);	//取得交付事件的流水號
			sql = "INSERT INTO deltask (deliverySN,isDeliver,deliveryComment,deliveryTarSN,deplmainSN,souSysSN,tarSysSN,statusSN) VALUES ("+deliverySN+",?,?,?,?,?,?,?)";
			ps = conn.prepareStatement( sql );
			ps.setString(1,"t");
			ps.setString(2, fod.getComment() );
			ps.setInt(3, tarDeliverySN);
			ps.setInt(4, localDeplMainSN);
			ps.setInt(5, localSysemId);
			ps.setInt(6, targetSystemId);
			ps.setInt(7, 1);	//啟動交付中
			ps.execute();
			
			//產生交付事件<分公司>
			sql = "INSERT INTO deltask (deliverySN,isDeliver,deliveryComment,deliveryTarSN,deplmainSN,souSysSN,tarSysSN,statusSN) VALUES ("+tarDeliverySN+",?,?,?,?,?,?,?)";
			ps = tarConn.prepareStatement( sql );
			ps.setString(1,"f");
			ps.setString(2, fod.getComment() );
			ps.setInt(3, deliverySN);
			ps.setInt(4, tarDeplMainSN);
			ps.setInt(5, 6);
			ps.setInt(6, 7);
			ps.setInt(7, 1);	//啟動交付中
			ps.execute();
			
			//FTP連線建立
			session = jsch.getSession(ftpId, ftpUrl);
    		session.setPassword( ftpPwd );
    		session.connect();
			sftp = (ChannelSftp) session.openChannel("sftp");
			sftp.connect();
			sftp.cd(ftpPath);
			
			//SVM連線建立 
			final ArrayList<String> connInfo = wid.getSVN4SubsysConnInfo(subsysId, 7);
			
			souRepo = SVNRepositoryFactory.create( SVNURL.parseURIEncoded( connInfo.get(0) ) );
			ISVNAuthenticationManager authManager = SVNWCUtil.createDefaultAuthenticationManager(connInfo.get(1), connInfo.get(2) );
			souRepo.setAuthenticationManager(authManager);
	        
			tarRepo = SVNRepositoryFactory.create( SVNURL.parseURIEncoded( tarUrl ) );
			authManager = SVNWCUtil.createDefaultAuthenticationManager(tarUsr, tarPwd);
		    tarRepo.setAuthenticationManager(authManager);
		    
			editor1 = souRepo.getCommitEditor(null,new CommitMediator());
			editor2 = tarRepo.getCommitEditor(null,new CommitMediator());
			editor1.openRoot(-1);
			editor2.openRoot(-1);
			
			//開始處理檔案
			for (int i=0;i<fod.getFiles().size();i++){
				final String fn = fod.getFiles().get(i).getS2();	//部署檔
				if ( !"".equalsIgnoreCase( fn )  && fn!=null ) {
					final String depFile = fod.getFiles().get(i).getS1();
					final int detailId = i+1;
					/**
					 *  安忍不動如大地 靜慮深密如秘藏
					 * 1. 寫入F部署細項
					 * 2. 簽入SVN CM OL 部署檔區
					 * 3. 
					 * */ 
					
					//寫入F部署細項
					sql = "INSERT INTO fdepldetail (fDeplmainSN, fDeplDetailId,fileName) VALUES(?,?,?)";		
					ps = conn.prepareStatement(sql);
					ps.setInt(1, fDeplmainSN );
					ps.setInt(2, detailId);
					ps.setString(3, fn);
					ps.execute();
					
					//	開始將FTP的檔案簽入SVN CM OL 部署檔區
					input1 = sftp.get(fn);
					input2 = sftp.get(fn);
					try {
		        		editor1.addFile( depFile,null,-1);
			        } catch(Exception e) {
			        	editor1.openFile( depFile,-1);
			        }
					try {
		        		editor2.addFile( depFile,null,-1);
			        } catch(Exception e) {
			        	editor2.openFile( depFile,-1);
			        }
					
					final byte[] bytes = IOUtils.toByteArray(input1);
					
					editor1.applyTextDelta( depFile, null ); 
					editor2.applyTextDelta( depFile, null );
		    		final String baseChecksum1 = deltaGenerator.sendDelta( depFile, input1, editor1, true);
		    		final String baseChecksum2 = deltaGenerator.sendDelta( depFile, input2, editor2, true);
		    	    editor1.textDeltaEnd( depFile );
		    	    editor2.textDeltaEnd( depFile );
		    	    editor1.closeFile( depFile, baseChecksum1 );
		    	    editor2.closeFile( depFile, baseChecksum2 );
					//	寫入部署細項<研究院>
					sql = "INSERT INTO depldetail (deplmainSN,depldetailid,localFile,isnonweb,localPath,depPath,localVer,depOrder,comReqsNum,creator,createdDateTime,fileSizeInByte) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, localDeplMainSN);  //deplmainSN
					ps.setInt(2, detailId);			//depldetailid
					ps.setString(3, depFile);			//localFile
					ps.setString(4, "t");			//isnonweb
					ps.setString(5, "");			//localPath
					ps.setString(6, "");			//depPath
					ps.setInt(7, 0);				//版次先寫0，等等簽入完再更新
					ps.setInt(8, 0);				//depOrder
					ps.setInt(9, fod.getReqs().size());				//comReqsNum
					ps.setString(10, empId);
					ps.setTimestamp(11, wid.cDT() );
					ps.setInt(12, bytes.length);
					ps.execute();
					
					//寫入部署細項<分公司>
					sql = "INSERT INTO depldetail (deplmainSN,depldetailid,localFile,isnonweb,localPath,depPath,localVer,depOrder,comReqsNum,creator,createdDateTime,fileSizeInByte) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
					ps = tarConn.prepareStatement(sql);
					ps.setInt(1, tarDeplMainSN);  //deplmainSN
					ps.setInt(2, detailId);			//depldetailid
					ps.setString(3, depFile);			//localFile
					ps.setString(4, "t");			//isnonweb
					ps.setString(5, "");			//localPath
					ps.setString(6, "");			//depPath
					ps.setInt(7, 0);				//版次先寫0，等等簽入完再更新
					ps.setInt(8, 0);				//depOrder
					ps.setInt(9, fod.getReqs().size());				//comReqsNum
					ps.setString(10, empId);
					ps.setTimestamp(11, wid.cDT() );
					ps.setInt(12, bytes.length);
					ps.execute();
					
					//記錄FTP與SVN CM OL部署檔的對應關係<研究院>
					sql = "INSERT INTO tmpMPdeplF (fDeplMainSN,fDeplDetailId,deplmain,depldetailid) VALUES (?,?,?,?)";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, fDeplmainSN );
					ps.setInt(2, detailId);
					ps.setInt(3, localDeplMainSN );
					ps.setInt(4, detailId);
					ps.execute();
					
					//記錄SVN CM OL 與 分公司SVN的對應關係<研究院>
					final int svnMappingSN = wid.getCurrentSN("delsvn");
					sql = "INSERT INTO delsvn (svnMappingSN,deliverySN,fromPath,fromFile,fromVer,toPath,toFile,toVer,sysSN,fromSVNhost,toSVNhost) VALUES ("+svnMappingSN+",?,?,?,?,?,?,?,?,?,?)";
					ps = conn.prepareStatement(sql);
		    	    ps.setInt(1, deliverySN);														/*	交付方 交付代碼		*/											
		    	    ps.setString(2, "" );						/*	交付方	SVN相對路徑		*/
		    	    ps.setString(3, depFile );							/*	交付方 檔案名稱		*/
		    	    ps.setInt(4, 0 );								/*	交付方	版次		*/
		    	    ps.setString(5, "" );						/*	接收方 SVN相對路徑		*/
		    	    ps.setString(6, depFile );							/*	接收方 檔案名稱		*/
		    	    ps.setLong(7, 0);															/*	接收方	版次		*/
		    	    ps.setInt(8, targetSystemId);															/*	交付端給了誰 (他給了誰)		*/
		    	    ps.setString(9, connInfo.get(0) );				/*	交付方 主機		*/
		    	    ps.setString(10, tarUrl );			/*	接收方 主機		*/
		    	    ps.execute();
		    	    
		    	    //記錄SVN CM OL 與 分公司SVN的對應關係<分公司>
					final int tarSvnMappingSN = wid.getTarCurrentSN("delsvn",tarConn);
					sql = "INSERT INTO delsvn (svnMappingSN,deliverySN,fromPath,fromFile,fromVer,toPath,toFile,toVer,sysSN,fromSVNhost,toSVNhost) VALUES ("+tarSvnMappingSN+",?,?,?,?,?,?,?,?,?,?)";
					ps = tarConn.prepareStatement(sql);
		    	    ps.setInt(1, tarDeliverySN);														/*	交付方 交付代碼		*/											
		    	    ps.setString(2, "" );						/*	交付方	SVN相對路徑		*/
		    	    ps.setString(3, depFile );							/*	交付方 檔案名稱		*/
		    	    ps.setInt(4, 0 );								/*	交付方	版次		*/
		    	    ps.setString(5, "" );						/*	接收方 SVN相對路徑		*/
		    	    ps.setString(6, depFile );							/*	接收方 檔案名稱		*/
		    	    ps.setLong(7, 0);															/*	接收方	版次		*/
		    	    ps.setInt(8, 6);															/*	交付端給了誰 (他給了誰)		*/
		    	    ps.setString(9, connInfo.get(0) );				/*	交付方 主機		*/
		    	    ps.setString(10, tarUrl );			/*	接收方 主機		*/
		    	    ps.execute();
				}
			}
			info1 = editor1.closeEdit();			//本次提交結束
			info2 = editor2.closeEdit();			//本次提交結束
			
			//將版次資訊更新<研究院>
			sql = "UPDATE depldetail SET localVer=? WHERE deplmainSN=?";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, info1.getNewRevision());
			ps.setInt(2, localDeplMainSN);
			ps.execute();
			
			//將版次資訊更新<分公司>
			sql = "UPDATE depldetail SET localVer=? WHERE deplmainSN=?";
			ps = tarConn.prepareStatement(sql);
			ps.setLong(1, info2.getNewRevision());
			ps.setInt(2, tarDeplMainSN);
			ps.execute();
			
			//版次對應資訊更新<研究院>
			sql = "UPDATE delsvn SET fromVer=?,toVer=? WHERE deliverySN=?";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, info1.getNewRevision() );
			ps.setLong(2, info2.getNewRevision() );
			ps.setInt(3, deliverySN);
			ps.execute();
			
			//版次對應資訊更新<分公司>
			ps = tarConn.prepareStatement(sql);
			ps.setLong(1, info1.getNewRevision() );
			ps.setLong(2, info2.getNewRevision() );
			ps.setInt(3, tarDeliverySN);
			ps.execute();
			
			
			//修改交付狀態資訊
			sql = "UPDATE deltask SET statusSN=3 WHERE deliverySN=?";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, deliverySN );
			ps.execute();
			ps = tarConn.prepareStatement(sql);
			ps.setLong(1, tarDeliverySN );
			ps.execute();
			
			// 寫入需求
			for (int i=0;i<fod.getReqs().size();i++){
				final String reqSID = fod.getReqs().get(i).getReqSID();
				final String reqDesc = fod.getReqs().get(i).getReqDesc();
				final int reqStatus = wid.isReqExisted(reqSID, reqDesc);
				
				if( reqStatus == 3 ) {	//3 是不存在，所以要新增入需求的記錄中
					// 記錄需求<研究院>
					sql = "INSERT INTO reqs (reqDesc,reqSID,subsysId,creatorId,cDT,sysSN) VALUES (?,?,?,?,?,?)";
					ps = conn.prepareStatement(sql);
					ps.setString(1, reqDesc );
					ps.setString(2, reqSID );
					ps.setString(3, subsysId );
					ps.setString(4, empId );
					ps.setTimestamp(5, wid.cDT() );
					ps.setInt(6, localSysemId );
					ps.execute();
					
					// 記錄需求<分公司>
					ps = tarConn.prepareStatement(sql);
					ps.setString(1, reqDesc );
					ps.setString(2, reqSID );
					ps.setString(3, subsysId );
					ps.setString(4, empId );
					ps.setTimestamp(5, wid.cDT() );
					ps.setInt(6, 7 );	//因為在 shield中，研究院帳務所的代號是7
					ps.execute();
				}
				sql = "SELECT reqId FROM reqs WHERE reqSID=?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, reqSID );
				rs = ps.executeQuery();
				rs.next();
				final int reqId = rs.getInt("reqId");
				
				ps = tarConn.prepareStatement(sql);
				ps.setString(1, reqSID );
				rs = ps.executeQuery();
				rs.next();
				final int tarReqId = rs.getInt("reqId");
				
				for (int j=0;j<fod.getFiles().size();j++){
					final String fn = fod.getFiles().get(j).getS2();	//部署檔
					if ( !"".equalsIgnoreCase( fn ) ) {
						final int detailId = j+1;
						//記錄 需求所包含部署程式<研究院>
						sql = "INSERT INTO mpreqfiles (deplmainSN,depldetailid,reqSN,creator,createdDateTime) VALUES (?,?,?,?,?)";
						ps = conn.prepareStatement(sql);
						ps.setInt(1, localDeplMainSN );
						ps.setInt(2, detailId);
						ps.setInt(3, reqId);
						ps.setString(4, empId);
						ps.setTimestamp(5, wid.cDT() );
						ps.execute();
						//記錄 需求所包含部署程式<分公司>
						ps = tarConn.prepareStatement(sql);
						ps.setInt(1, tarDeplMainSN );
						ps.setInt(2, detailId);
						ps.setInt(3, tarReqId);
						ps.setString(4, empId);
						ps.setTimestamp(5, wid.cDT() );
						ps.execute();
					}
				}
			}
			
			
			
			for (int j=0;j<fod.getFiles().size();j++){
				final String fn = fod.getFiles().get(j).getS2();	//部署檔
				if ( !"".equalsIgnoreCase( fn ) ) {
					final int detailId = j+1;
					//記錄 程式部署至Squad<研究院>
					sql = "INSERT INTO mpdeplunitsqu (deplmainSN,depldetailid,bssid,subsysId,squadId,creator,createdDateTime) VALUES(?,?,?,?,?,?,?)";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, localDeplMainSN );
					ps.setInt(2, detailId);
					ps.setString(3, "fOrder");
					ps.setString(4, subsysId);
					ps.setString(5, "SquadF_D");		//第一個squadId
					ps.setString(6, empId);
					ps.setTimestamp(7, wid.cDT() );
					ps.execute();
					
					ps.setString(5, "SquadF_D2");		//第二個squadId
					ps.execute();
					
					//記錄 程式部署至Squad<分公司>
					ps = tarConn.prepareStatement(sql);
					ps.setInt(1, tarDeplMainSN );
					ps.setInt(2, detailId);
					ps.setString(3, "fOrder");
					ps.setString(4, subsysId);
					ps.setString(5, "SquadF_D");		//第一個squadId
					ps.setString(6, empId);
					ps.setTimestamp(7, wid.cDT() );
					ps.execute();
					ps.setString(5, "SquadF_D2");		//第二個squadId
					ps.execute();	
				}
			}
		} catch(Exception e) {
			wid.errLog(e);
		} finally {
			try {
				if ( input1 !=null ) {input1.close();}
				if ( input2 !=null ) {input2.close();}
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}
				if ( tarConn !=null ) {tarConn.close();}
				if ( sftp != null ) { if ( sftp.isConnected() ) { sftp.disconnect(); } }
				if ( session != null ) { if ( session.isConnected() ) { session.disconnect(); } }
				if ( souRepo!= null ) { souRepo.closeSession(); }
				if ( tarRepo!= null ) { tarRepo.closeSession(); }
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		
		response.getWriter().write("OK");
	}

}
